package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.po.AllotPo;
import cn.gson.crm.model.po.StaffPo;

	/**
	 * 分配
	 * */
public class AllotDao {

	/**
	 * 根据员工id获取分配的学员
	 * 
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public List<AllotPo> getAllotList(String allotId) throws SQLException {
		List<Map<String, Object>> listData = null;
		String sql = "select a.allot_id, a.allot_staff_id ,a.allot_time,a.allot_student_id,b.student_name,  "
				+ " DATEDIFF(max(c.follow_again_time),NOW()) as a from allot a LEFT OUTER JOIN student b  ON a.allot_student_id = b.student_id "
				+ " LEFT JOIN follow c ON a.allot_student_id = c.follow_student_id WHERE a.allot_staff_id = ? "
				+ " group by a.allot_student_id ORDER BY a asc ";
		listData = Jdbc.getInstace().executeQueryForList(sql,allotId);
		List<AllotPo> allotList = null;
		if (listData != null && listData.size() > 0) {
			allotList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				String s = data.get("allot_time").toString().split(" ")[0];
				AllotPo allotPo = new AllotPo();
				if(data.get("a")!=null){
					allotPo.setAllotAgain(type(data.get("a")));
				}
				allotPo.setAllotId(type(data.get("allot_id")));
				allotPo.setAllotStaffId(type(data.get("allot_staff_id")));
				allotPo.setAllotStudentId(type(data.get("allot_student_id")));
				allotPo.setAllotStudentName((String)data.get("student_name"));
				allotPo.setAllotTime(java.sql.Date.valueOf(s));
				allotList.add(allotPo);
			}
		}
		return allotList;
	}
	
	/**
	 * 查询所有的咨询师的名字和id
	 * */
	public List<StaffPo> getStaffList() throws SQLException {
		List<Map<String, Object>> listData = null;
		String sql = "select * from staff where satff_status <> 0";
		listData = Jdbc.getInstace().executeQueryForList(sql);
		List<StaffPo> staffList = null;
		if (listData != null && listData.size() > 0) {
			staffList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				staffList.add(
						new StaffPo(
								0,
								(String)data.get("staff_name"),
								0,
								Integer.parseInt(data.get("staff_id").toString())
								)
						);
			}
		}
		return staffList;
	}
	
	/**
	 * 查询所有的咨询师的名字和id
	 * */
	public List<StaffPo> getStaffOtherList(String staffid) throws SQLException {
		List<Map<String, Object>> listData = null;
		String sql = "select * from staff where satff_status <> 0 and staff_id <> ?";
		listData = Jdbc.getInstace().executeQueryForList(sql,staffid);
		List<StaffPo> staffList = null;
		if (listData != null && listData.size() > 0) {
			staffList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				staffList.add(
						new StaffPo(
								0,
								(String)data.get("staff_name"),
								0,
								Integer.parseInt(data.get("staff_id").toString())
								)
						);
			}
		}
		return staffList;
	}
	
	public boolean insertAllot(String stuid ,String staid) throws SQLException{
		String sql = "insert into allot(allot_student_id,allot_staff_id) value(?,?)";
		return Jdbc.getInstace().executeUpdate(sql,stuid,staid);
	}
	
	public boolean updateAllot(String staid , String stuid) throws SQLException{
		String sql = "update allot set allot_staff_id = ? where allot_student_id=?";
		return Jdbc.getInstace().executeUpdate(sql,staid,stuid);
	}
	
	public String getSource(int sourceId) throws SQLException{
		String sql = "select source_name from source where source_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, sourceId);
		String sourceName = "";
		if(map != null){
			sourceName = (String)map.get("source_name");
	}
		return sourceName;
	}
	
	public String getInteresting(int interestingId) throws SQLException{
		String sql = "select interesting_name from interesting where interesting_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, interestingId);
		String interestingName = " ";
		if (map != null) {
			interestingName = (String) map.get("interesting_name");
		}
		return interestingName;
	}
	
	public String getStaffName(int staffId) throws SQLException{
		String sql = "select staff_name from staff where staff_id = ?";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, staffId);
		String allotName = " ";
		if(map != null){
			allotName = (String)map.get("staff_name");
	}
		return allotName;
	}


		/**
		 * 字符串转为int
		 * */
		protected int type(Object object) {
			return Integer.parseInt(object.toString());
		}
}
